/*
 Navicat Premium Data Transfer

 Source Server         : demo
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : localhost:3306
 Source Schema         : 2107A-month1

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 25/09/2023 11:33:22
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for city
-- ----------------------------
DROP TABLE IF EXISTS `city`;
CREATE TABLE `city` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of city
-- ----------------------------
BEGIN;
INSERT INTO `city` (`id`, `name`) VALUES (1, '济南');
INSERT INTO `city` (`id`, `name`) VALUES (2, '聊城');
INSERT INTO `city` (`id`, `name`) VALUES (3, '晋城');
INSERT INTO `city` (`id`, `name`) VALUES (4, '太原');
INSERT INTO `city` (`id`, `name`) VALUES (5, '郑州');
INSERT INTO `city` (`id`, `name`) VALUES (6, '洛阳');
INSERT INTO `city` (`id`, `name`) VALUES (7, '石家庄');
INSERT INTO `city` (`id`, `name`) VALUES (8, '邯郸');
INSERT INTO `city` (`id`, `name`) VALUES (9, '张家口');
INSERT INTO `city` (`id`, `name`) VALUES (10, '南京');
INSERT INTO `city` (`id`, `name`) VALUES (11, '苏州');
INSERT INTO `city` (`id`, `name`) VALUES (12, '徐州');
COMMIT;

-- ----------------------------
-- Table structure for goods_brand
-- ----------------------------
DROP TABLE IF EXISTS `goods_brand`;
CREATE TABLE `goods_brand` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of goods_brand
-- ----------------------------
BEGIN;
INSERT INTO `goods_brand` (`id`, `name`) VALUES (1, 'google');
INSERT INTO `goods_brand` (`id`, `name`) VALUES (2, 'huawei');
INSERT INTO `goods_brand` (`id`, `name`) VALUES (3, 'apple');
COMMIT;

-- ----------------------------
-- Table structure for goods_category
-- ----------------------------
DROP TABLE IF EXISTS `goods_category`;
CREATE TABLE `goods_category` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of goods_category
-- ----------------------------
BEGIN;
INSERT INTO `goods_category` (`id`, `name`) VALUES (1, '手机');
INSERT INTO `goods_category` (`id`, `name`) VALUES (2, '电脑');
INSERT INTO `goods_category` (`id`, `name`) VALUES (3, '耳机');
COMMIT;

-- ----------------------------
-- Table structure for goods_info
-- ----------------------------
DROP TABLE IF EXISTS `goods_info`;
CREATE TABLE `goods_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_id` int DEFAULT NULL,
  `category_id` int DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `num` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `price` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `flag` int DEFAULT NULL COMMENT '0未上架 1 已上架',
  `create_user` int DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL,
  `update_user` datetime DEFAULT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `indx_b_c` (`brand_id`,`category_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of goods_info
-- ----------------------------
BEGIN;
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (1, 1, 1, '商品1', '121334', '11', 1, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (2, 1, 2, '商品2', '12313', '1', 1, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (3, 2, 2, '商品3', '123123', '111', 0, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (4, 2, 1, '商品4', '1231312341', '111', 1, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (5, 1, 2, '商品5', '12312312', '1111', 1, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (6, 1, 1, 'good123', '7de4c66e-ef75-4633-bc1e-75065c61aa99', '1223', 1, NULL, NULL, NULL, NULL);
INSERT INTO `goods_info` (`id`, `brand_id`, `category_id`, `name`, `num`, `price`, `flag`, `create_user`, `create_time`, `update_user`, `update_time`) VALUES (7, 1, 1, 'goods555', 'bb5d201b-0bc6-41fa-923a-592e31eb255f', '555', 1, 1, NULL, NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for province
-- ----------------------------
DROP TABLE IF EXISTS `province`;
CREATE TABLE `province` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of province
-- ----------------------------
BEGIN;
INSERT INTO `province` (`id`, `name`) VALUES (1, '山东');
INSERT INTO `province` (`id`, `name`) VALUES (2, '山西');
INSERT INTO `province` (`id`, `name`) VALUES (3, '河北');
INSERT INTO `province` (`id`, `name`) VALUES (4, '河南');
INSERT INTO `province` (`id`, `name`) VALUES (5, '江苏');
COMMIT;

-- ----------------------------
-- Table structure for qu
-- ----------------------------
DROP TABLE IF EXISTS `qu`;
CREATE TABLE `qu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3;

-- ----------------------------
-- Records of qu
-- ----------------------------
BEGIN;
INSERT INTO `qu` (`id`, `name`) VALUES (1, '城南');
INSERT INTO `qu` (`id`, `name`) VALUES (2, '城北');
INSERT INTO `qu` (`id`, `name`) VALUES (3, '城东');
INSERT INTO `qu` (`id`, `name`) VALUES (4, '城西');
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `province_id` int DEFAULT NULL,
  `city_id` int DEFAULT NULL,
  `qu_id` int DEFAULT NULL,
  `group_str` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `inx_s_s_q` (`province_id`,`city_id`,`qu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (1, 'Jason Martinez', 3, 2, 3, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (2, 'Monica Schmidt', 5, 2, 2, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (3, 'Frederick White', 3, 2, 2, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (4, 'Hsuan Cho Yee', 2, 1, 2, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (5, 'Chen Lu', 5, 3, 2, '5组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (6, 'Debra Sanders', 2, 2, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (7, 'Ashley Bryant', 2, 1, 3, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (8, 'Gary Hawkins', 3, 3, 2, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (9, 'Du Xiuying', 3, 2, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (10, 'Tang Wing Suen', 1, 2, 1, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (11, 'Man Yun Fat', 4, 1, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (12, 'Song Yunxi', 5, 2, 2, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (13, 'Wada Kenta', 4, 2, 1, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (14, 'Zhang Anqi', 1, 2, 2, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (15, 'Kobayashi Nanami', 3, 3, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (16, 'Du Zhennan', 5, 2, 1, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (17, 'Xiao Xiuying', 4, 2, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (18, 'Xia Zhennan', 4, 2, 1, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (19, 'Lawrence Gutierrez', 1, 2, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (20, 'Tsang Hui Mei', 3, 2, 2, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (21, 'Rebecca Hawkins', 1, 1, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (22, 'Wei Xiaoming', 1, 1, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (23, 'Ying Chi Ming', 1, 2, 3, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (24, 'Miura Shino', 2, 3, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (25, 'Heung Tsz Ching', 3, 2, 1, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (26, 'Ye Yunxi', 3, 3, 2, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (27, 'Jamie Black', 4, 2, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (28, 'Li Zhiyuan', 3, 3, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (29, 'Dennis Richardson', 4, 1, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (30, 'Elizabeth Nichols', 1, 1, 2, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (31, 'Ren Lu', 4, 2, 1, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (32, 'Uchida Sara', 3, 1, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (33, 'Edith Woods', 3, 1, 2, '5组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (34, 'Xiang Zhiyuan', 1, 1, 1, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (35, 'Ueda Tsubasa', 1, 2, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (36, 'Tang Wing Sze', 4, 1, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (37, 'Yeow Wing Fat', 4, 1, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (38, 'Chow Kwok Yin', 4, 2, 1, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (39, 'Matsui Hana', 3, 2, 3, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (40, 'Ishikawa Takuya', 3, 3, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (41, 'Cao Zhennan', 5, 1, 2, '1组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (42, 'Au Chun Yu', 2, 2, 3, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (43, 'Chad West', 5, 3, 3, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (44, 'Allen Ford', 4, 2, 3, '5组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (45, 'Kwong Ka Man', 2, 2, 3, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (46, 'Steven Dunn', 4, 1, 2, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (47, 'Nakajima Mai', 5, 2, 1, '2组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (48, 'Chic Tsz Ching', 3, 2, 2, '3组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (49, 'Nakamura Hana', 5, 3, 3, '4组');
INSERT INTO `student` (`id`, `name`, `province_id`, `city_id`, `qu_id`, `group_str`) VALUES (50, 'Tong Chi Ming', 4, 3, 3, '4组');
COMMIT;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `salt` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `nick_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `avatar_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `gitee_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `name`, `password`, `salt`, `nick_name`, `avatar_url`, `gitee_id`) VALUES (1, '张三', '7328a29bceceb9dc57a1392c7e2045fc', '8355abbb', 'Ikun', 'https://foruda.gitee.com/avatar/1695113588109380793/12065236_hccxx_1695113588.png', 12065236);
COMMIT;

-- ----------------------------
-- Procedure structure for zzt
-- ----------------------------
DROP PROCEDURE IF EXISTS `zzt`;
delimiter ;;
CREATE PROCEDURE `zzt`()
BEGIN
SELECT count(*) as ydata, p.name as xdata 
from student   s
LEFT JOIN province p on  s.province_id = p.id

GROUP BY s.province_id;
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for zztByParam
-- ----------------------------
DROP PROCEDURE IF EXISTS `zztByParam`;
delimiter ;;
CREATE PROCEDURE `zztByParam`(IN g varchar(255))
BEGIN
SELECT count(*) as ydata, p.name as xdata 
from student   s
LEFT JOIN province p on  s.province_id = p.id

where s.group_str = g
GROUP BY s.province_id;
END
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;
